USE Fresher14_G1
------
GO
	IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='sp_GelAllAccount' AND TYPE ='P')
	DROP PROC sp_GelAllAccount
	GO
	CREATE PROC sp_GelAllAccount
	AS
	BEGIN
		SElECT * FROM Account ORDER BY Username
	END
-------
GO
	IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='sp_CheckUserExisted' AND TYPE ='P')
	DROP PROC sp_CheckUserExisted
	GO
	CREATE PROC sp_CheckUserExisted
	@Username VARCHAR(50)
	AS 
	SELECT * FROM Account WHERE Username=@Username
-------
GO
	IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='sp_CheckUser' AND TYPE ='P')
	DROP PROC sp_CheckUser
	GO
	CREATE PROC sp_CheckUser
	@Username VARCHAR(50),
	@Password VARCHAR(50)
	AS
	SELECT COUNT(*) FROM Account WHERE Username=@Username AND Password=@Password
-------
GO
	IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='sp_CreateAccount' AND TYPE ='P')
	DROP PROC sp_InsertAccount
	GO
	CREATE PROC sp_InsertAccount
	@RoleID uniqueidentifier,
	@Username VARCHAR(50),
	@Password VARCHAR(50),
	@FullName NVARCHAR(50),
	@PhoneNumber1 VARCHAR(20),
	@PhoneNumber2 VARCHAR(20),
	@Gender NVARCHAR(20),
	@IsActive BIT
	AS
	INSERT INTO Account
	VALUES(NEWID(),@RoleID,
	@Username,
	@Password,
	@FullName,
	@PhoneNumber1,
	@PhoneNumber2,
	@Gender,
	@IsActive)
-------
GO
	IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='sp_UpdateAccount' AND TYPE ='P')
	DROP PROC sp_UpdateAccount
	GO
	CREATE PROC sp_UpdateAccount
	@AccountId UNIQUEIDENTIFIER,
	@RoleID UNIQUEIDENTIFIER,
	@FullName NVARCHAR(50),
	@PhoneNumber1 VARCHAR(20),
	@PhoneNumber2 VARCHAR(20),
	@Gender NVARCHAR(20),
	@IsActive BIT
	AS
	UPDATE Account
	SET
	RoleID = @RoleID,
	FullName = @FullName,
	PhoneNumber1 = @PhoneNumber1,
	PhoneNumber2 = @PhoneNumber2,
	Gender = @Gender,
	IsActive = @IsActive
	WHERE AccountId = @AccountId
-------
GO 
	IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='sp_SetIsActiveAccount' AND TYPE ='P')
	DROP PROC sp_SetIsActive
	GO
	CREATE PROC sp_SetIsActive
	@AccountId UNIQUEIDENTIFIER
	@IsActive BIT
	AS
	UPDATE Account SET IsActive = @IsActive WHERE AccountId = @AccountId
-------	
GO
	IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='sp_GetPasswordByUserName' AND TYPE ='P')
	DROP PROC sp_GetPasswordByUserName
	GO
	CREATE PROC sp_GetPasswordByUserName
	@Username VARCHAR(50)
	AS 
	SELECT * FROM Account where Username=@Username
-------
GO
	IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='sp_ChangePass' AND TYPE ='P')
	DROP PROC sp_ChangePass
	GO
	CREATE PROC sp_ChangePass
	@Username VARCHAR(50),
	@Password VARCHAR(50)
	AS
	UPDATE Account SET [Password]=@Password
	WHERE Username= @Username
-------
GO
	IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='sp_SearchUser' AND TYPE ='P')
	DROP PROC sp_SearchUser
	GO
	CREATE PROC sp_SearchUser
	@Username VARCHAR(50)
	AS 
	SELECT * FROM Account WHERE Username like '%'+ @Username +'%'
